-- EXEC SP_CUMMULATIVE_COST_MODEL
ALTER PROC SP_CUMMULATIVE_COST_MODEL
AS 
BEGIN
	CREATE TABLE #COST_TABLE ( Part_Number VARCHAR(50), Fab_Cost NUMERIC(18,4), FAB_Yield NUMERIC(18,4), GDW INT, 
							BUMP_COST NUMERIC(18,4), BUMP_YIELD NUMERIC(18,4), BUMP_OUT_COST NUMERIC(18,4),
							Sort_Cost NUMERIC(18,4), SORT_Yield NUMERIC(18,4), SORT_OUT_COST NUMERIC(18,4),
							ASSEMBLY_COST NUMERIC(18,4), ASSEMBLY_YIELD NUMERIC(18,4), BOM_COST NUMERIC(18,4), ASSEMBLY_OUT_COST NUMERIC(18,4),
							FT_Cost NUMERIC(18,4), FT_Yield NUMERIC(18,4), FT_OUT_COST NUMERIC(18,4),
							FG_COST NUMERIC(18,4) )

	CREATE TABLE #PATH_TABLE (ROW_ID INT, PATH_NUMBER INT ,PROCESSSTEP_ID INT, PS_TYPE VARCHAR(50), STD_COST NUMERIC(10,4), YIELD FLOAT, YIELDED_STD_COST NUMERIC(10,4))
	CREATE TABLE #PROCESSSTEPS_CUMM_COST_TABLE ( LOT_ID INT, PROCESSSTEP_ID INT, CUMM_STD_COST NUMERIC(10,4), YIELDED_STD_COST NUMERIC(10,4), PS_TYPE_NAME VARCHAR(50), IS_TRANSIT INT )

	DECLARE @FG_CURSOR CURSOR
	DECLARE @PATH_CURSOR CURSOR 
	DECLARE @BOM_CURSOR CURSOR
	DECLARE @FG_PART_NUMBER VARCHAR(50)
	DECLARE @FG_PROCESSSTEP_ID INT
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @FAB_COST NUMERIC(18,4)
	DECLARE @FAB_YIELD NUMERIC(18,4)
	DECLARE @GDW INT
	DECLARE @BUMP_COST NUMERIC(18,4)
	DECLARE @BUMP_YIELD NUMERIC(18,4), @BUMP_OUT_COST NUMERIC(18,4)
	DECLARE @SORT_COST NUMERIC(18,4), @SORT_OUT_COST NUMERIC(18,4)
	DECLARE @SORT_YIELD NUMERIC(18,4)
	DECLARE @ASSEMBLY_COST NUMERIC(18,4), @ASSEMBLY_OUT_COST NUMERIC(18,4)
	DECLARE @ASSEMBL_YIELD NUMERIC(18,4)
	DECLARE @FT_COST NUMERIC(18,4), @FT_OUT_COST NUMERIC(18,4)
	DECLARE @FT_YIELD NUMERIC(18,4)
	DECLARE @FG_COST NUMERIC(18,4)
	DECLARE @QUARTER_NO		INT
	DECLARE @MEM_DIE_QTY INT, @MEM_PROCESSSTEP_ID INT, @PATH_PROCESSSTEP_ID INT, @FAB_PROCESSSTEP_ID INT
	DECLARE @MEM_DIE_COST NUMERIC(18,4), @MEM_COST NUMERIC(18,4), @TOTAL_STD_COST NUMERIC(18,4)
	DECLARE @P_TYPE VARCHAR(20)
	DECLARE @YEAR		INT
	DECLARE @STD_COST NUMERIC(10,4)
	DECLARE @YIELD FLOAT

	-- This calculation is for die that are being used as BOM in other assemblies
	-- THIS IS TO TAKE OF DIE BANK PARTS THAT ARE USED IN MULTIPLE PRODUCTS
	INSERT INTO #PROCESSSTEPS_CUMM_COST_TABLE (LOT_ID, PROCESSSTEP_ID, PS_TYPE_NAME)
		SELECT -1, BOM_ProcessStep_ID, 'Die Bank' FROM assembly_bom_setup_table ABST, Product P, Process_Step PS WHERE ABST.Bom_ProcessStep_Id = P.ProcessStep_ID AND
		P.ProcessStep_ID = PS.ProcessStep_ID and P.P_Type IS NULL AND PS.PS_TYPE = 'Die Bank'
--------------------------------------------------------------------------------------------------------------------------------------------------------
	DECLARE @PROCESS_STEPS_CURSOR CURSOR
	
	SET @PROCESS_STEPS_CURSOR = CURSOR FOR 
							SELECT DISTINCT PROCESSSTEP_ID FROM #PROCESSSTEPS_CUMM_COST_TABLE ORDER BY PROCESSSTEP_ID DESC
	
	OPEN @PROCESS_STEPS_CURSOR
	FETCH NEXT FROM @PROCESS_STEPS_CURSOR INTO @PROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO #PATH_TABLE(ROW_ID, PATH_NUMBER, PROCESSSTEP_ID, PS_TYPE, STD_COST, YIELD)
		SELECT ROW_ID, PATH_NUMBER, XYZ.PROCESSSTEP_ID, PS_TYPE, 
			(CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			 END) AS STD_COST,
			(CASE WHEN @QUARTER_NO = 1 THEN Q1_YIELD
				WHEN @QUARTER_NO = 2 THEN Q2_YIELD
				WHEN @QUARTER_NO = 3 THEN Q3_YIELD
				ELSE Q4_YIELD
			 END) AS YIELD
			 FROM 
			(SELECT Distinct ROW_ID, PATH_NUMBER, PROCESSSTEP_ID, PS_TYPE 
				FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@PROCESSSTEP_ID,0) ) AS XYZ 
			LEFT OUTER JOIN UPDATE_VIEW_COST_TABLE UVCT ON XYZ.PROCESSSTEP_ID = UVCT.PROCESSSTEP_ID AND YEAR(fin_year) = @YEAR
			 ORDER BY PATH_NUMBER, ROW_ID DESC
		
		SET @PATH_CURSOR = CURSOR FOR 
							SELECT PROCESSSTEP_ID, PS_TYPE, STD_COST, YIELD FROM #PATH_TABLE WHERE PATH_NUMBER = 1 ORDER BY ROW_ID DESC
		OPEN @PATH_CURSOR
		FETCH NEXT FROM @PATH_CURSOR INTO @PATH_PROCESSSTEP_ID, @PS_TYPE, @STD_COST, @YIELD
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF ( @PS_TYPE = 'DIE BANK' )
			BEGIN
				SELECT @FAB_PROCESSSTEP_ID = PROCESSSTEP_ID FROM #PATH_TABLE WHERE PS_TYPE = 'FAB'
				SELECT @GDW = PARAMETER_VALUE FROM Process_Parameter WHERE PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID AND PARAMETER_KEY = 'Gross Die/Wafer'
				IF ( @GDW > 0 )
				BEGIN
					SET @TOTAL_STD_COST = ISNULL(@TOTAL_STD_COST,0)/@GDW
				END
			END

			SET @TOTAL_STD_COST = (ISNULL(@TOTAL_STD_COST,0) + (ISNULL(@STD_COST,0)) )/( @YIELD/100.00 )
			FETCH NEXT FROM @PATH_CURSOR INTO @PATH_PROCESSSTEP_ID, @PS_TYPE, @STD_COST, @YIELD
		END
		CLOSE @PATH_CURSOR
		DEALLOCATE @PATH_CURSOR
		
		DELETE FROM #PATH_TABLE
		
		UPDATE #PROCESSSTEPS_CUMM_COST_TABLE SET CUMM_STD_COST = @TOTAL_STD_COST WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
		
		SET @TOTAL_STD_COST = 0
		FETCH NEXT FROM @PROCESS_STEPS_CURSOR INTO @PROCESSSTEP_ID
	END
	CLOSE @PROCESS_STEPS_CURSOR
	DEALLOCATE @PROCESS_STEPS_CURSOR

--------------------------------------------------------------------------------------------------------------------------------------------------------	
	SET @FG_CURSOR = CURSOR FOR SELECT P.PART_NUMBER, P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID 
	AND PS.PS_Type = 'FG' AND PS.ProcessStep_ID NOT IN ( SELECT ProcessStep_ID FROM Process_Parameter WHERE Parameter_KEY = 'IsChipset' and Parameter_Value = 'TRUE' )

	SELECT @YEAR = CC.CC_YEAR, @QUARTER_NO = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR CQC, COMPANY_CALENDAR CC
		WHERE CQC.CQC_YEARID = CC.CC_YEARID 
		AND CURRENT_TIMESTAMP >= CQC_START_DATE 
		AND CURRENT_TIMESTAMP <= CQC_END_DATE

	OPEN @FG_CURSOR
	FETCH NEXT FROM @FG_CURSOR INTO @FG_PART_NUMBER, @FG_PROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @TOTAL_STD_COST = 0.0
		INSERT INTO #PATH_TABLE(ROW_ID, PATH_NUMBER, PROCESSSTEP_ID, PS_TYPE, STD_COST, YIELD)
		SELECT ROW_ID, PATH_NUMBER, XYZ.PROCESSSTEP_ID, PS_TYPE, 
			(CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			 END) AS STD_COST,
			(CASE WHEN @QUARTER_NO = 1 THEN Q1_YIELD
				WHEN @QUARTER_NO = 2 THEN Q2_YIELD
				WHEN @QUARTER_NO = 3 THEN Q3_YIELD
				ELSE Q4_YIELD
			 END) AS YIELD
			 FROM 
			(SELECT Distinct ROW_ID, PATH_NUMBER, PROCESSSTEP_ID, PS_TYPE 
				FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@FG_PROCESSSTEP_ID,0) ) AS XYZ 
			LEFT OUTER JOIN UPDATE_VIEW_COST_TABLE UVCT ON XYZ.PROCESSSTEP_ID = UVCT.PROCESSSTEP_ID AND YEAR(fin_year) = @YEAR
			 ORDER BY PATH_NUMBER, ROW_ID DESC

		SET @PATH_CURSOR = CURSOR FOR 
							SELECT PROCESSSTEP_ID, PS_TYPE, STD_COST, YIELD FROM #PATH_TABLE WHERE PATH_NUMBER = 1 ORDER BY ROW_ID DESC
		OPEN @PATH_CURSOR
		FETCH NEXT FROM @PATH_CURSOR INTO @PROCESSSTEP_ID, @PS_TYPE, @STD_COST, @YIELD
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF ( @YIELD IS NULL OR @YIELD = 0 )
			BEGIN
				SET @YIELD = 100.00
			END

			IF ( @PS_TYPE = 'FAB' )
			BEGIN
				SET @FAB_COST  = @STD_COST
				SET @FAB_YIELD = @YIELD
				IF @FG_PROCESSSTEP_ID = 146
				BEGIN
					PRINT @STD_COST
					PRINT @YIELD
					PRINT @TOTAL_STD_COST
				END
				SELECT @GDW = Parameter_Value FROM Process_Parameter WHERE ProcessStep_ID = @PROCESSSTEP_ID AND Parameter_Key = 'Gross Die/Wafer'
			END
			ELSE IF ( @PS_TYPE = 'Bumping' )
			BEGIN
				SET @BUMP_COST  = @STD_COST
				SET @BUMP_YIELD = @YIELD
				SET @BUMP_OUT_COST =  (@TOTAL_STD_COST + @BUMP_COST )/(@BUMP_YIELD/100.00)
			END
			ELSE IF ( @PS_TYPE = 'SORT' )			
			BEGIN
				IF @FG_PROCESSSTEP_ID = 146
				BEGIN
					PRINT @STD_COST
					PRINT @YIELD
					PRINT @TOTAL_STD_COST
				END
				SET @SORT_COST  = @STD_COST
				SET @SORT_YIELD = @YIELD
				SET @SORT_OUT_COST = (@TOTAL_STD_COST + @SORT_COST )/(@SORT_YIELD/100.00)
			END
			ELSE IF ( @PS_TYPE = 'DIE BANK' )
			BEGIN
				IF ( @GDW > 0 )
				BEGIN
					SET @TOTAL_STD_COST = ISNULL(@TOTAL_STD_COST,0)/@GDW
				END
			END
			ELSE IF ( @PS_TYPE = 'Assembly' )
			BEGIN
				SET @ASSEMBLY_COST  = @STD_COST
				SET @ASSEMBL_YIELD = @YIELD

				SET @BOM_CURSOR = CURSOR FOR SELECT ISNULL(Qty,1), bom_processStep_ID, p_type FROM assembly_bom_setup_table abst, product p WHERE abst.bom_processstep_id = p.processstep_id and Assembly_ProcessStep_Id = @PROCESSSTEP_ID
				OPEN @BOM_CURSOR
				FETCH NEXT FROM @BOM_CURSOR INTO @MEM_DIE_QTY, @MEM_PROCESSSTEP_ID,@P_TYPE
				WHILE @@FETCH_STATUS = 0
				BEGIN
					IF @P_TYPE = 'BOM'
					BEGIN
						SELECT @MEM_DIE_COST = (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
										WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
										WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
										ELSE Q4_STANDARD_COST
									 END)  FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = @MEM_PROCESSSTEP_ID
					END
					ELSE
					BEGIN
						SELECT @MEM_DIE_COST = CUMM_STD_COST FROM #PROCESSSTEPS_CUMM_COST_TABLE WHERE PROCESSSTEP_ID = @MEM_PROCESSSTEP_ID
					END
					SET @MEM_COST = ISNULL(@MEM_COST, 0.0 ) + ISNULL(@MEM_DIE_COST,0) * @MEM_DIE_QTY
					FETCH NEXT FROM @BOM_CURSOR INTO @MEM_DIE_QTY, @MEM_PROCESSSTEP_ID, @P_TYPE
				END
				CLOSE @BOM_CURSOR
				DEALLOCATE @BOM_CURSOR
				SET @ASSEMBLY_OUT_COST = ((ISNULL(@TOTAL_STD_COST,0)+ ISNULL(@MEM_COST,0) )/( @YIELD/100.00)) +  ISNULL(@STD_COST,0)
			END
			ELSE IF ( @PS_TYPE = 'FT' )
			BEGIN
				SET @FT_COST  = @STD_COST
				SET @FT_YIELD = @YIELD
				SET @FT_OUT_COST = (ISNULL(@TOTAL_STD_COST,0) + (ISNULL(@STD_COST,0)) )/( @YIELD/100.00 )			
			END

			IF @PS_TYPE = 'Assembly'
			BEGIN
				SET @TOTAL_STD_COST = ((ISNULL(@TOTAL_STD_COST,0)+ ISNULL(@MEM_COST,0) )/( @YIELD/100.00)) +  ISNULL(@STD_COST,0)
			END
			ELSE
			BEGIN
				SET @TOTAL_STD_COST = (ISNULL(@TOTAL_STD_COST,0) + (ISNULL(@STD_COST,0)) )/( @YIELD/100.00 )
			END
			
			FETCH NEXT FROM @PATH_CURSOR INTO @PROCESSSTEP_ID, @PS_TYPE, @STD_COST, @YIELD
		END
		CLOSE @PATH_CURSOR
		DEALLOCATE @PATH_CURSOR

		DELETE FROM #PATH_TABLE		

		INSERT INTO #COST_TABLE ( Part_Number, Fab_Cost, FAB_Yield, GDW, 
							BUMP_COST, BUMP_YIELD, BUMP_OUT_COST,
							Sort_Cost, SORT_Yield, SORT_OUT_COST,
							ASSEMBLY_COST, ASSEMBLY_YIELD, BOM_COST, ASSEMBLY_OUT_COST,
							FT_Cost, FT_Yield, FT_OUT_COST,
							FG_COST )
							VALUES( @FG_PART_NUMBER, @FAB_COST, @FAB_YIELD, @GDW, @BUMP_COST, @BUMP_YIELD, @BUMP_OUT_COST, @SORT_COST, @SORT_YIELD, @SORT_OUT_COST,
							@ASSEMBLY_COST, @ASSEMBL_YIELD, @MEM_COST, @ASSEMBLY_OUT_COST, @FT_COST, @FT_YIELD, @FT_OUT_COST, @TOTAL_STD_COST )

			SET @FAB_COST = 0.0
			SET @FAB_YIELD = 0.0
			SET @BUMP_COST = 0.0
			SET @BUMP_YIELD = 0.0
			SET @SORT_COST = 0.0
			SET @SORT_YIELD = 0.0
			SET @ASSEMBLY_COST = 0.0
			SET @ASSEMBL_YIELD = 0.0
			SET @FT_COST = 0.0
			SET @FT_YIELD = 0.0
			SET @MEM_COST = 0.0
			SET @GDW = 1	
			SET @BUMP_OUT_COST = 0.0
			SET @SORT_OUT_COST = 0.0
			SET @ASSEMBLY_OUT_COST = 0.0
			SET @FT_OUT_COST = 0.0
			SET @MEM_COST = 0.0
		FETCH NEXT FROM @FG_CURSOR INTO @FG_PART_NUMBER, @FG_PROCESSSTEP_ID
	END
	CLOSE @FG_CURSOR
	DEALLOCATE @FG_CURSOR
	SELECT Part_Number AS [PART NUMBER], Fab_Cost AS [FAB COST], (FAB_Yield/100) AS [FAB YIELD], GDW AS[GROSS DIE PER WAFER], 
							BUMP_COST AS [BUMP COST], (BUMP_YIELD/100) AS [BUMP YIELD], BUMP_OUT_COST AS [BUMP OUT COST],
							Sort_Cost AS [SORT COST], (SORT_Yield/100) AS [SORT YIELD], SORT_OUT_COST AS [SORT OUT COST],
							ASSEMBLY_COST AS [ASSEMBLY COST], (ASSEMBLY_YIELD/100) AS [ASSEMBLY YIELD], BOM_COST AS [BOM COST], ASSEMBLY_OUT_COST AS [ASSEMBLY OUT COST],
							FT_Cost AS [FT COST], (FT_Yield/100) AS [FT YIELD], FT_OUT_COST AS [FT OUT COST],
							FG_COST AS [FG COST] FROM #COST_TABLE
END